package SQL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

public class SQLLiteHandler {
	private final String fDB;
	private Connection fConnection = null;
	public static final String SQL_CONCURRENCY ="is not unique";
	public SQLLiteHandler(String db) {
		fDB = db;
	}

	private Connection connect() {
		Connection c = null;
		try {
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:" + fDB + ".db");
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		try {
			c.setAutoCommit(true);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return c;
	}


	@SuppressWarnings("unchecked")
	public JSONArray Query(String query) throws RuntimeException{
		System.out.println(query);
		JSONArray result = new JSONArray();
		if (fConnection == null) {
			fConnection = this.connect();
		}
		ResultSet rs = null;
		Statement stmt = null;

		try {
			stmt = fConnection.createStatement();
			rs = stmt.executeQuery(query);
			ResultSetMetaData rsmd = rs.getMetaData();

			while (rs.next()) {
				JSONObject set = new JSONObject();
				for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
					set.put(rsmd.getColumnName(i),
							rs.getString(rsmd.getColumnName(i)));
				}
				result.add(set);
			}
			stmt.close();
			fConnection.close();
			fConnection = null;
		} catch (SQLException e) {
			try {		
				//	stmt = fConnection.createStatement();
				//	stmt.executeUpdate(query);
				stmt.close();
				fConnection.close();
				fConnection = null;
				if (e.getMessage().toString().contains(SQL_CONCURRENCY)){
					throw new RuntimeException(SQL_CONCURRENCY);
				}
			} catch (SQLException e1) {
				if (e.getMessage().toString().contains(SQL_CONCURRENCY)){
					throw new RuntimeException(SQL_CONCURRENCY);
				}
			}

		}

		return result;
	}

	public static String clean(String dirty) {
		String dirty2 = dirty.replaceAll("\\\\+'", "'");
		return dirty2.replaceAll("'", "\\\\'");
	}
}
